package com.ppp.searchHelp.DB;

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ppp.searchHelp.core.CloudsearchDocHelp;
import com.ppp.searchHelp.util.AppConfig;
import com.ppp.searchHelp.util.StringUtil;

public class DBOperating {

	private static final ThreadLocal<Connection> conn_holder = new ThreadLocal<>();

	private static final String indexDRIVER;
	private static final String indexURL;
	private static final String indexUSER;
	private static final String indexPASSWORD;

	private static final String addSQL;
	private static final String updateSQL;
	private static final String deleteSQL;

	private static final String updateIndexStatusSQL;
	private static final String status;

	static {
		indexDRIVER = AppConfig.getStr("jdbc.driver");
		indexURL = AppConfig.getStr("jdbc.url");
		indexUSER = AppConfig.getStr("jdbc.username");
		indexPASSWORD = AppConfig.getStr("jdbc.password");

		addSQL = AppConfig.getStr("add");
		updateSQL = AppConfig.getStr("update");
		deleteSQL = AppConfig.getStr("delete");

		updateIndexStatusSQL = AppConfig.getStr("updateIndexStatusSQL");
		status = AppConfig.getStr("status");

		try {
			Class.forName(indexDRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取数据库连接
	 * 
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		Connection conn = conn_holder.get();
		if (conn == null) {
			conn = DriverManager.getConnection(indexURL, indexUSER, indexPASSWORD);
			conn.setAutoCommit(false);
			conn_holder.set(conn);
		}
		return conn;
	}

	/**
	 * 关闭
	 * 
	 * @throws SQLException
	 */
	public static void closeConnection() throws SQLException {
		Connection conn = conn_holder.get();
		if (conn != null) {
			conn.close();
			conn_holder.remove();
		}
	}

	/**
	 * 任务调用的方法
	 * 
	 * 
	 * 
	 */
	public void run() {
		Connection conn = null;
		CloudsearchDocHelp help = new CloudsearchDocHelp();
		try {
			
			conn = getConnection();
			
			List<String> ids = new ArrayList<>();
			if (!StringUtil.isBlank(addSQL)) {
				addToHelp(addSQL, help, ids, 1);
			}

			if (!StringUtil.isBlank(updateSQL)) {
				addToHelp(updateSQL, help, ids, 2);
			}

			if (!StringUtil.isBlank(deleteSQL)) {
				addToHelp(deleteSQL, help, ids, 3);
			}
			
			if (!ids.isEmpty()) {
				help.push();
				updateDB(ids,updateIndexStatusSQL,status);
				conn.commit();
			}
			
			

		} catch (SQLException | IOException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				closeConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public List<Map<String, Object>> getIndexFromDB(String sql) throws SQLException {

		Connection conn = getConnection();
		PreparedStatement stmt = conn.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		List<Map<String, Object>> map = build(rs);
		closeQuietly(stmt);
		return map;
	}

	/**
	 * 
	 * @param sql
	 * @param help
	 * @param ids
	 * @param operation
	 *            1-add;2-update;3-remove
	 * @throws SQLException
	 */
	public void addToHelp(String sql, CloudsearchDocHelp help, List<String> ids, int operation) throws SQLException {
		List<Map<String, Object>> addMap = getIndexFromDB(sql);
		for (Map<String, Object> map : addMap) {
			ids.add(map.get("id").toString());
			switch (operation) {
			case 1:
				help.add(map);
				break;
			case 2:
				help.update(map);
				break;
			case 3:
				help.remove(map);
				break;
			}

		}
	}

	public void updateDB(List<String> ids, String sql, String status) throws SQLException {
		Connection conn = getConnection();
		PreparedStatement pst = null;
		for (String id : ids) {
			pst = conn.prepareStatement(sql);
			pst.setObject(1, status);
			pst.setObject(2, id);
			pst.executeUpdate();
			closeQuietly(pst);
		}
	}

	static final void closeQuietly(Statement st) {
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
			}
		}
	}

	public static final List<Map<String, Object>> build(ResultSet rs) throws SQLException {
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		String[] labelNames = new String[columnCount + 1];
		int[] types = new int[columnCount + 1];
		buildLabelNamesAndTypes(rsmd, labelNames, types);
		while (rs.next()) {
			Map<String, Object> attrs = new HashMap<>();
			for (int i = 1; i <= columnCount; i++) {
				Object value;
				if (types[i] < Types.BLOB)
					value = rs.getObject(i);
				else if (types[i] == Types.CLOB)
					value = handleClob(rs.getClob(i));
				else if (types[i] == Types.NCLOB)
					value = handleClob(rs.getNClob(i));
				else if (types[i] == Types.BLOB)
					value = handleBlob(rs.getBlob(i));
				else
					value = rs.getObject(i);

				attrs.put(labelNames[i], value);
			}
			result.add(attrs);
		}
		return result;
	}

	private static final void buildLabelNamesAndTypes(ResultSetMetaData rsmd, String[] labelNames, int[] types)
			throws SQLException {
		for (int i = 1; i < labelNames.length; i++) {
			labelNames[i] = rsmd.getColumnLabel(i);
			types[i] = rsmd.getColumnType(i);
		}
	}

	public static String handleClob(Clob clob) throws SQLException {
		if (clob == null)
			return null;

		Reader reader = null;
		try {
			reader = clob.getCharacterStream();
			char[] buffer = new char[(int) clob.length()];
			reader.read(buffer);
			return new String(buffer);
		} catch (IOException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				reader.close();
			} catch (IOException e) {
				throw new RuntimeException(e);
			}
		}
	}

	public static byte[] handleBlob(Blob blob) throws SQLException {
		if (blob == null)
			return null;

		InputStream is = null;
		try {
			is = blob.getBinaryStream();
			byte[] data = new byte[(int) blob.length()]; // byte[] data = new
															// byte[is.available()];
			is.read(data);
			is.close();
			return data;
		} catch (IOException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				is.close();
			} catch (IOException e) {
				throw new RuntimeException(e);
			}
		}
	}

}
